import re
import time
import json
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial
import logging
from redash import models, limiter
from redash.permissions import (
    require_permission,
    require_admin_or_owner,
    is_admin_or_owner,
    require_permission_or_owner,
    require_admin_default,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class CustomerResource(BaseResource):
    def get(self, code):
        try:
            customertemp = models.CustomerTemp.get_by_code(code,self.current_org)
            return customertemp.to_dict()
        except IntegrityError as e:
            logging.debug("CustomerResource: %s", str(e))
            abort(503, message= str(e))

class CustomerTempListResource(BaseResource):
    def get(self,bytstatus):
        try:
            results = models.CustomerTemp.get_by_status(bytstatus,self.current_org).order_by(models.CustomerTemp.strcustomercode.asc())
            return [t.to_dict() for t in results]
        except IntegrityError as e:
            logging.debug("CustomerTempListResource: %s", str(e))
            abort(503, message= str(e))

class CustomerNewResource(BaseResource):
    def get(self):
        result = models.CustomerTemp.get_id()
        return result[0]

    @require_admin_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("strcustomercode","strcustomername"))
        customertemp = models.CustomerTemp(
        lngcustomerid = 0,
        org=self.current_org,
        strcustomercode = req["strcustomercode"],
        strcustomername = req["strcustomername"],
        lngcustomertypeid =0,
        strcustomertypecode = '',
        strcustomertypename = '',
        representative =  req["representative"],
        mobile =  req["mobile"],
        address =  req["address"],
        blnisinactive = req["blnisinactive"],
        bytstatus = 2,
        blnchuli = 0,
        )

        hrpsql="""
            insert into customer(LNGCUSTOMERID, STRCUSTOMERCODE, STRCUSTOMERNAME, STRCATEGORY, LNGCUSTOMERTYPEID, 
            BLNISINACTIVE, LNGTERMID, DBLCREDITLIMIT, STRLASTFCDATE, LNGLASTFCRECEIPTID, 
            DBLDISCOUNTRATE, LNGEMPLOYEEID, LNGAREAID, STRCONTACTNAME, LNGTITLEID, 
            STROFFICEPHONENUMBER, STRHOMEPHONENUMBER, STRFAXNUMBER,  STREMAIL, STRTAXNO, 
            STRBILLTOADDRESS, STRBILLTOPOSTALCODE, STRLICENCENO, STRELICENCENO, STRCERTIFICATE,
            STRNOTES, STRSTARTDATE, LNGARACCOUNTID, LNGAPACCOUNTID, LNGARDISCOUNTACCOUNTID, 
            LNGAPDISCOUNTACCOUNTID, BLNISPAYDISCOUNT, BLNISQUANDISCOUNT, BLNISSALEDISCOUNT, BLNISALLITEM,
            STRITEMCODESTART, STRITEMCODEEND, STRCREATEDATE, STRCLOSEDATE, STRCUSTOMERAUXCODE, 
            LNGPRICEGRADEID, STRLICENCEDATE, STRBUSINESSLICENCEDATE, STRCHECKOUTDATE, BLNPREAPPROVE, 
            BLNCUSTOMERPREAPPROVE, BLNSUPPLYPREAPPROVE, STRLICENCEMSG, STRQUALITYPMSG, STRMOVEPHONE, 
            STRCARDNO, STRBIMID, BLNSHOWEINFORMATION, LNGACCOUNTSUBJECTTYPEID)
            select customer_seq.nextval, '"""+req["strcustomercode"]+"""', '"""+req["strcustomername"]+"""', '1', (select nvl(max(lngcustomertypeid),0) From customertype where blnisdetail=1 and blnisinactive=0), 
            0, 0, 0, ' ', 0, 
            100, 0, 0, ' ', 0, 
            ' ', ' ', ' ', ' ', ' ',
            nvl('"""+req["address"]+"""',' '), ' ', ' ', ' ', ' ', 
            ' ', '2022-06-26', 0, 560, 0, 
            0, 0, 0, 0, 1,
            ' ', ' ', '2022-06-26', ' ', ' ',
            0, '2037-04-10', '2024-12-29', ' ', 0,
            0, 0, ' ', ' ', ' ',
            ' ', ' ', 1, 7 from dual WHERE NOT EXISTS(SELECT 'X' FROM CUSTOMER WHERE STRCUSTOMERCODE='"""+req["strcustomercode"]+"""')"""
        strsql = """ select strcustomercode From public.customertemp where strcustomername='"""+req["strcustomername"]+"""' """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            if result is None :
                try:
                    models.db.session.add(customertemp)
                    models.db.session.commit()
                except IntegrityError as e:
                    if "strcustomercode" in str(e):
                        abort(400, message="单位编号已存在.")
                    abort(500)
            else :
                abort(400, message="单位名称已存在.")
        except :
            abort(400, message="单位名称已存在.")
        strsql = """select  value from public.globalsetting where  type='接口' and code='合同单位回写HRP' and value ='1'"""
        try:
            result = models.Contracts.get_selcontracts(strsql)
            if result is None :
                return ;
        except :
            abort(400, message=strsql)
        strsql=""" select  strcustomername from customer
        where strcustomercode = '"""+req["strcustomercode"]+"""'"""
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(strsql, self.current_user)
            if error :
                return str(error);
            json_object = json.loads(result)
            if len(json_object["rows"])>0 :
                strsql=""" update customer set strcustomername='"""+req["strcustomername"]+"""' 
                where strcustomercode = '"""+req["strcustomercode"]+"""'"""
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(strsql, self.current_user)
                return ;
        except :
            return;
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(hrpsql, self.current_user)
        except :
            return;


class CustomerTempResource(BaseResource):
    def get(self, customertemp_id):
        try:
            result = models.CustomerTemp.get_by_id(customertemp_id)
            return result.to_dict()
        except IntegrityError as e:
            logging.debug("CustomerTempResource: %s", str(e))
            abort(503, message= str(e))

    @require_admin_default
    def post(self, customertemp_id):
        req = request.get_json(force=True)
        require_fields(req, ("strcustomercode","strcustomername"))

        customertemp = models.CustomerTemp.get_by_id(customertemp_id)
        params = dict(strcustomercode = req["strcustomercode"],  strcustomername = req["strcustomername"],
                representative =  req["representative"],mobile =  req["mobile"],address =  req["address"],
                blnisinactive = req["blnisinactive"])

        hrpsql="""
            insert into customer(LNGCUSTOMERID, STRCUSTOMERCODE, STRCUSTOMERNAME, STRCATEGORY, LNGCUSTOMERTYPEID, 
            BLNISINACTIVE, LNGTERMID, DBLCREDITLIMIT, STRLASTFCDATE, LNGLASTFCRECEIPTID, 
            DBLDISCOUNTRATE, LNGEMPLOYEEID, LNGAREAID, STRCONTACTNAME, LNGTITLEID, 
            STROFFICEPHONENUMBER, STRHOMEPHONENUMBER, STRFAXNUMBER,  STREMAIL, STRTAXNO, 
            STRBILLTOADDRESS, STRBILLTOPOSTALCODE, STRLICENCENO, STRELICENCENO, STRCERTIFICATE,
            STRNOTES, STRSTARTDATE, LNGARACCOUNTID, LNGAPACCOUNTID, LNGARDISCOUNTACCOUNTID, 
            LNGAPDISCOUNTACCOUNTID, BLNISPAYDISCOUNT, BLNISQUANDISCOUNT, BLNISSALEDISCOUNT, BLNISALLITEM,
            STRITEMCODESTART, STRITEMCODEEND, STRCREATEDATE, STRCLOSEDATE, STRCUSTOMERAUXCODE, 
            LNGPRICEGRADEID, STRLICENCEDATE, STRBUSINESSLICENCEDATE, STRCHECKOUTDATE, BLNPREAPPROVE, 
            BLNCUSTOMERPREAPPROVE, BLNSUPPLYPREAPPROVE, STRLICENCEMSG, STRQUALITYPMSG, STRMOVEPHONE, 
            STRCARDNO, STRBIMID, BLNSHOWEINFORMATION, LNGACCOUNTSUBJECTTYPEID)
            select customer_seq.nextval, '"""+req["strcustomercode"]+"""', '"""+req["strcustomername"]+"""', '1', (select nvl(max(lngcustomertypeid),0) From customertype where blnisdetail=1 and blnisinactive=0), 
            0, 0, 0, ' ', 0, 
            100, 0, 0, ' ', 0, 
            ' ', ' ', ' ', ' ', ' ',
            nvl('"""+req["address"]+"""',' '), ' ', ' ', ' ', ' ', 
            ' ', '2022-06-26', 0, 560, 0, 
            0, 0, 0, 0, 1,
            ' ', ' ', '2022-06-26', ' ', ' ',
            0, '2037-04-10', '2024-12-29', ' ', 0,
            0, 0, ' ', ' ', ' ',
            ' ', ' ', 1, 7 from dual WHERE NOT EXISTS(SELECT 'X' FROM CUSTOMER WHERE STRCUSTOMERCODE='"""+req["strcustomercode"]+"""')"""
        strsql = """ select strcustomercode From public.customertemp where id<>"""+customertemp_id+""" and strcustomername='"""+req["strcustomername"]+"""' """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            if result is None :
                try:
                    self.update_model(customertemp, params)
                    models.db.session.commit()
                except :
                    abort(400, message="单位编号已存在.")
            else :
                abort(400, message="单位名称已存在.")
        except :
            abort(400, message="单位编号已存在.")
        strsql = """select  value from public.globalsetting where  type='接口' and code='合同单位回写HRP' and value='1'"""
        try:
            result = models.Contracts.get_selcontracts(strsql)
            if result is None :
                return ;
        except :
            abort(400, message=strsql)
        strsql="""select  strcustomername from customer
        where strcustomercode = '"""+req["strcustomercode"]+"""'"""
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(strsql, self.current_user)
            if error :
                return str(error);
            json_object = json.loads(result)
            if len(json_object["rows"])>0 :
                strsql=""" update customer set strcustomername='"""+req["strcustomername"]+"""' 
                where strcustomercode = '"""+req["strcustomercode"]+"""'"""
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(strsql, self.current_user)
                return ;
        except :
            return;
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(hrpsql, self.current_user)
            # if error:
            #     return str(error)+':'+hrpsql;
        except :
            return;

class CustomerTempdeleteResource(BaseResource):
    @require_admin_default
    def post(self, customertemp_id):
        customertemp =  models.CustomerTemp.get_by_id(customertemp_id)

        result = models.CustomerTemp.get_by_customertempid(customertemp_id)
        if result.rowcount > 0 :
            abort(400, message="单位已使用，不能删除！")
            return
        models.db.session.delete(customertemp)
        models.db.session.commit()



class CustomerListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        strcustomercode = req["strcustomercode"]
        strcustomername = req["strcustomername"]
        blnisinactive = req["blnisinactive"] if "blnisinactive" in req else "true"
        orderfield = req["field"] if "field" in req else ""
        if orderfield=="strcustomercode" :
            orderfield ="t1.strcustomercode"
        elif orderfield=="strcustomername":
            orderfield ="t1.strcustomername"
        elif orderfield=="representative":
            orderfield ="t1.representative"
        elif orderfield=="mobile":
            orderfield ="t1.mobile"
        elif orderfield=="address":
            orderfield ="t1.address"
        elif orderfield=="blnisinactive":
            orderfield ="t1.blnisinactive"
        else:
            orderfield ="t1.strcustomercode"
        order = req["order"] if "order" in req else 0

        strwhere ="t1.org_id=" +str(self.current_org.id)
        if strcustomercode !="" :
            strwhere = strwhere + " and t1.strcustomercode like '%"+strcustomercode+"%'"
        if strcustomername !='' :
            strwhere = strwhere + " and t1.strcustomername like '%"+strcustomername+"%'"
        if blnisinactive !="all" :
            strwhere = strwhere + " and t1.blnisinactive = "+blnisinactive
        strgetfield =""" t1.*"""

        tblname = """public.customertemp t1 """
        #orderfield ="t1.strcustomercode"
        result = models.ContractApply.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]
######################################################################
class EmployeeResource(BaseResource):
    def get(self, code):
        try:
            employeetemp = models.EmployeeTemp.get_by_code(code,self.current_org)
            return employeetemp.to_dict()
        except IntegrityError as e:
            logging.debug("EmployeeResource: %s", str(e))
            abort(503, message= str(e))

class EmployeeListResource(BaseResource):
    # def get(self,bytstatus):
    #   return [t.to_dict() for t in results]
    def post(self):
        req = request.get_json(force=True)
        code = req["code"]
        name = req["name"]
        orgunit_id=req["orgunit_id"] if "orgunit_id" in req else 0
        
        userid = str(req["userid"])  if "userid" in req else "0"
        strsql ="""select t1.id,t1.code,t1.name,t1.orgunit_id
            From  public.users t1 where  ',' || array_to_string(t1.groups, ',') || ',' not like '%' || (select COALESCE(max(id),0) From groups where name ='super_admin') || '%'
            and t1.org_id=""" +str(self.current_org.id)
        if code !='' :
            strsql = strsql + " and t1.code like '%"+code+"%'"
        if name !='' :
            strsql = strsql + " and t1.name like '%"+name+"%'"
        if orgunit_id !=0 :
            strsql = strsql + " and t1.orgunit_id = '"+str(orgunit_id)+"'"
        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("EmployeeListResource: %s", str(e))
            abort(503, message= str(e))
#######################################################################
# class DepartmentResource(BaseResource):
#     def get(self, code):
#         departmenttemp = models.DepartmentTemp.get_by_code(code)
#         return departmenttemp.to_dict()


class DepartmentListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"]
        name = req["name"]
        userid = str(req["userid"])  if "userid" in req else "0"
        strsql ="""select id,code,
        case when (select COALESCE(max(value),'0') from public.globalsetting where  type='部门选择' and code='显示部门编码')='1' then code || ' ' || name else name end as name
            From public.orgunit where org_id=""" +str(self.current_org.id)
        if code !='' :
            strsql = strsql + " and code like '%"+code+"%'"
        if name !='' :
            strsql = strsql + " and name like '%"+name+"%'"
        strsql = strsql +' order by code,name '
        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("DepartmentListResource: %s", str(e))
            abort(503, message= str(e))
    # def get(self,bytstatus):
    #   results = models.DepartmentTemp.get_by_status(bytstatus).order_by(models.DepartmentTemp.strdepartmentcode.asc())
    #   return [t.to_dict() for t in results]
####################################################################
class yusuancodeListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"] if "code" in req else "0"
        userid = str(req["userid"])  if "userid" in req else "0"
        contractid = str(req["contractid"])  if "contractid" in req else "0"
        strsql ="""select *From public.outlaybudgetview where org_id=""" +str(self.current_org.id) +""" 
         and 
        (strdepartmentcode in (select t1.code From public.orgunit t1 inner join public.users t2 on t1.id=t2.orgunit_id and t2.id="""+ userid+""")
        or code in(select budgetcode From public.contracts where id="""+ contractid+""")) order by code """
        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("yusuancodeListResource: %s", str(e))
            abort(503, message= str(e))
    # def get(self,bytstatus):
    #   results = models.DepartmentTemp.get_by_status(bytstatus).order_by(models.DepartmentTemp.strdepartmentcode.asc())
    #   return [t.to_dict() for t in results]
####################################################################
class EmployeepurchaseListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"]
        name = req["name"]
        userid = str(req["userid"])
        strsql ="""select t1.*
        from public.users t1 inner join public.orgunit t3 on t1.orgunit_id =t3.id and t1.org_id=t3.org_id
        inner join public.globalsetting t4 on t3.code=t4.value and t4.type='合同新建' and t4.code='采购经办人所属部门编码' and t3.org_id=t4.org_id
        where t1.responsible=0 and t1.org_id=""" +str(self.current_org.id)
        if code !='' :
            strsql = strsql + " and t1.code like '%"+code+"%'"
        if name !='' :
            strsql = strsql + " and t1.name like '%"+name+"%'"

        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("EmployeepurchaseListResource: %s", str(e))
            abort(503, message= str(e))
#######################################################################
class EmployeeshenjiListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"]
        name = req["name"]
        userid = str(req["userid"])
        strsql ="""select t1.*
        from public.users t1 inner join public.orgunit t3 on t1.orgunit_id =t3.id and t1.org_id=t3.org_id
        inner join public.globalsetting t4 on t3.code=t4.value and t4.type='合同用印申请单' and t4.code='审计部经办人所属部门编码' and t3.org_id=t4.org_id
        where t1.responsible=0 and t1.org_id=""" +str(self.current_org.id)
        if code !='' :
            strsql = strsql + " and t1.code like '%"+code+"%'"
        if name !='' :
            strsql = strsql + " and t1.name like '%"+name+"%'"

        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Contracts.get_selcontracts(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("EmployeeshenjiListResource: %s", str(e))
            abort(503, message= str(e))
